Chapter 1 Working with IDBac from Python

import plotly.offline as py
import plotly.graph_objects as go

fig = go.Figure(data=go.Bar(y=[2, 3, 1]))
fig.show()

py.plot(fig, filename = "data/python_output/1.html", auto_open=False)

## 'data/python_output/1.html'

import sqlite3
import json
import pandas as pd

conn = sqlite3.connect('C:/Users/CMC/Documents/GitHub/programming_idbac/data/databases/my_new_database.sqlite')
print(conn)

## <sqlite3.Connection object at 0x0000000029B004E0>

c = conn.cursor()

t = ('172-1',)
c.execute('SELECT min_mass FROM spectra WHERE strain_id=?', t)

## <sqlite3.Cursor object at 0x000000002AF102D0>

print(c.fetchall())

## [(1919,), (1919,), (60,), (60,)]

t = ('172-1',)
c.execute('SELECT peak_matrix FROM spectra WHERE strain_id=?', t)

## <sqlite3.Cursor object at 0x000000002AF102D0>

peaks = c.fetchone()

print(type(peaks))

## <class 'tuple'>

Get 172-1 sample peak lists as pandas df:

df = pd.read_sql_query('SELECT peak_matrix FROM spectra WHERE strain_id=:spec', conn, params = {'spec':'172-1'})

print(df)

##                                          peak_matrix
## 0  {"mass":[1919.93905,1925.71302,1945.99421,1959...
## 1  {"mass":[1919.93905,1925.91941,1946.20174,1959...
## 2  {"mass":[61.28432,70.25831,72.2628,74.2751,75....
## 3  {"mass":[61.28972,70.25253,72.25693,74.2751,75...

js = df.iloc[0,0]

a = json.loads(js)

type(a)

## <class 'dict'>

a = pd.DataFrame.from_dict(a)
print(a)

##             mass  intensity       snr
## 0     1919.93905   64.32751  75.24010
## 1     1925.71302   37.17911  43.48622
## 2     1945.99421    7.45092   8.71490
## 3     1959.29976   15.17239  17.74625
## 4     1973.28037   38.34066  44.84481
## ..           ...        ...       ...
## 165  10720.96664    2.97231   3.47653
## 166  11036.57515    2.58138   3.01929
## 167  11601.65465    2.68094   3.13574
## 168  12404.30272    3.16847   3.70597
## 169  20136.84362    2.63911   3.08681
## 
## [170 rows x 3 columns]

a['mass'][1] 

## 1925.71302